﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using NovinMedia.Data;

namespace QuickSQL.Controls
{
    public partial class DatabaseConnection : UserControl
    {
        public delegate void DatabasesFetchEventHandler(DbObject dbo, DataSet dsDatabases);
        public delegate void DatabaseLoadEventHandler(DbObject dbo, DataSet database);
        public event DatabasesFetchEventHandler DatabasesFetched;
        public event DatabaseLoadEventHandler DatabaseLoaded;
        public event Action<string> DatabaseOk;
        private bool _autoLoadDatabase = true;

        public bool AutoLoadDatabase
        {
            get { return _autoLoadDatabase; }
            set { _autoLoadDatabase = value; }
        }

        public DatabaseConnection()
        {
            InitializeComponent();
        }
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            SetFieldsEnabled();
        }

        private void authentication_CheckedChanged(object sender, EventArgs e)
        {
            SetFieldsEnabled();
        }


        private void SetFieldsEnabled()
        {
            SetFieldsEnabled(rbSQLServer.Checked);
        }
        private void SetFieldsEnabled(bool Enabled)
        {
            lblUsername.Enabled = Enabled;
            lblPassword.Enabled = Enabled;
            txtUsername.Enabled = Enabled;
            txtPassword.Enabled = Enabled;
        }

        public string GetConnectionString()
        {
            return GetConnectionString(SelectedDatabase ?? "master");
        }
        public string GetConnectionString(string DatabaseName)
        {
            string connStr = string.Empty;

            if (rbSQLServer.Checked == true)
            {
                connStr =
                    string.Format("Server={0:G};Database={1:G};User ID={2:G};Password={3:G};Trusted_Connection=False;Connection Timeout=120;",
                                  txtServerName.Text, DatabaseName, txtUsername.Text, txtPassword.Text);
            }
            else
                connStr =
                    string.Format("Data Source={0:G};Initial Catalog={1:G};Integrated Security=SSPI;Connection Timeout=120;",
                                  txtServerName.Text, DatabaseName);

            return connStr;
        }


        protected string GetServerVersion()
        {
            DbObject dbo = GetDbo();

            DataSet dsServer = dbo.RunQuery("SELECT SERVERPROPERTY('ProductVersion') as ServerVersion", "Server");

            string version = string.Empty;

            if (dsServer.Tables[0].Rows.Count > 0)
            {
                char[] seperatores = { '.' };
                string[] tmpVersion = dsServer.Tables[0].Rows[0]["ServerVersion"].ToString().Split(seperatores);
                version = tmpVersion[0];
            }
            return version;
        }

        public DbObject GetDbo()
        {
            return GetDbo("master");
        }
        public DbObject GetDbo(string databaseName)
        {
            string connStr = GetConnectionString(databaseName);
            DbObject dbo = new DbObject(connStr);
            return dbo;
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            string connStr = GetConnectionString();

            DbObject dbo = new DbObject(connStr);

            string databasesQuery = string.Empty;
            string serverVersion = GetServerVersion();
            if (int.Parse(serverVersion) <= 8)
            {
                databasesQuery = Properties.strDatabasesList2000;
            }
            else
            {
                databasesQuery = Properties.strDatabasesList2005;
            }

            //try
            {

                DataSet dsDatabases = dbo.RunQuery(databasesQuery, "Databases");
                cmbDatabases.DataSource = dsDatabases.Tables[0];
                cmbDatabases.ValueMember = "Text";
                cmbDatabases.DisplayMember = "Text";
                pnlDatabases.Enabled = true;
                if (DatabasesFetched != null) DatabasesFetched.Invoke(dbo, dsDatabases);
            }
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
        }

        public string ServerName
        {
            get
            {
                return txtServerName.Text;
            }
            set
            {
                txtServerName.Text = value;
            }
        }

        private void btnDatabaseLoad_Click(object sender, EventArgs e)
        {
            string databaseName = cmbDatabases.SelectedValue as string;
            DbObject dbObject = GetDbo(databaseName);
            if (dbObject != null)
            {
                if (AutoLoadDatabase)
                {
                    DataSet dsDatabase = dbObject.RunQuery(string.Format(Properties.strTablesList, databaseName), databaseName);
                    if (DatabaseLoaded != null) DatabaseLoaded.Invoke(GetDbo(databaseName), dsDatabase);
                }
                else
                {
                    if (DatabaseOk != null) DatabaseOk.Invoke(databaseName);
                }
            }
            else
            {
                MessageBox.Show("The connection couldn't be realized");
            }
        }

        public string SelectedDatabase
        {
            get
            {
                return cmbDatabases.SelectedValue as string;
            }
            set
            {
                if (!string.IsNullOrEmpty(value))
                {
                    cmbDatabases.SelectedValue = value;
                }
                else
                {
                    cmbDatabases.SelectedIndex = cmbDatabases.Items.Count > 0 ? 1 : -1;
                }

            }
        }

        public static class Properties
        {
            public static string DropProcedure = @"--
-- Dropping stored procedure {0:G} : 
--

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[{0:G}]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
  DROP PROCEDURE [dbo].[{0:G}]
  
GO";
            public static string ReturnScalarMethodTemplate = @"{5:G}public {0:G}int {1:G}({2:G})
		{{
			int RowsAffected = 0;
			int Result = 0;
			DbObject dbo = new DbObject();
			SqlParameter[] parameters = new SqlParameter[]
				{{
{3:G} 
				}};
			Result = dbo.RunProcedure(""{4:G}"", parameters, out RowsAffected);
			return Result;
		}}";
            public static string ReturnRowsAffectedMethodTemplate = @"{5:G}public {0:G}int {1:G}({2:G})
		{{
			int RowsAffected = 0;
			int Result = 0;
			DbObject dbo = new DbObject();
			SqlParameter[] parameters = new SqlParameter[]
				{{
{3:G} 
				}};
			Result = dbo.RunProcedure(""{4:G}"", parameters, out RowsAffected);
			return RowsAffected;
		}}";
            public static string ReturnDatasetMethodTemplate = @"{6:G}public {0:G}DataSet {1:G}({2:G})
		{{
			DbObject dbo = new DbObject();
			SqlParameter[] parameters = new SqlParameter[]
				{{
{3:G} 
				}};
			return dbo.RunProcedure(""{4:G}"", parameters, ""{5:G}"");
		}}";
            public static string SqlParameterTemplate = @"new SqlParameter(""{0:G}"",{1:G})";
            public static string strDatabasesList2005 = @"SELECT database_id AS ID, NULL AS ParentID, name AS Text FROM sys.databases ORDER BY [name]";
            public static string strDatabasesList2000 = @"SELECT dbid AS ID, NULL AS ParentID, name AS Text FROM [master].[dbo].[sysdatabases] ORDER BY [name]";
            public static string strTablesList = @"SELECT TABLE_NAME FROM [{0:G}].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'  
AND TABLE_NAME <> 'dtproperties'  
ORDER BY TABLE_NAME";
            public static string strTablesAndColumns = @"USE {0:G};

SELECT  Cols.TABLE_NAME,
		Cols.COLUMN_NAME,
		Cols.ORDINAL_POSITION,
		Cols.DATA_TYPE,
		Cols.NUMERIC_PRECISION,
		Cols.NUMERIC_SCALE,		
		Cols.IS_NULLABLE,
		Cols.CHARACTER_MAXIMUM_LENGTH,       
		COLUMNPROPERTY(object_id(Cols.TABLE_NAME), Cols.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
		( SELECT    COUNT(KCU.COLUMN_NAME)
		  FROM      INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
					INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON KCU.TABLE_NAME = TC.TABLE_NAME
																		  AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
																		  AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
		  WHERE     KCU.TABLE_NAME = Cols.TABLE_NAME
					AND KCU.COLUMN_NAME = Cols.COLUMN_NAME
		) AS IsIndex
FROM    [INFORMATION_SCHEMA].[COLUMNS] Cols
ORDER BY Cols.TABLE_NAME,
		Cols.ORDINAL_POSITION";
            public static string WrapperClass_CSharp_Template = @"using System;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using NovinMedia.Data;

namespace {0:G}
{{
	[DataObject(true)]
	public class {1:G}
	{{
{2:G}
	}}
}}";
        }
    }
}
